# -*- coding:utf-8 -*-

# 指定中文编码

######################################################################################
#                                                                                    #
#   读取csv文件生成json数据                                                             #
#                                                                                    #
######################################################################################

# import os
# import shutil
import xlrd
import xlwt

# from itertools import groupby

DOUBLE_PRECISION = 6  # 小数点个数

# Python 判断字符串是否为数字
# https://www.runoob.com/python3/python3-check-is-number.html
def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        pass

    try:
        import unicodedata

        unicodedata.numeric(s)
        return True
    except (TypeError, ValueError):
        pass

    return False


def is_float(s):
    return s.count(".") == 1


def to_value(s):
    # 转为字符串,并去掉尾部多余的0
    # https://stackoverflow.com/questions/2440692/formatting-floats-without-trailing-zeros
    strip_zero = lambda x: ("%f" % x).rstrip("0").rstrip(".")
    if is_number(s):
        ss = strip_zero(float(s))
        if is_float(ss):
            return float(ss)
        else:
            return int(ss)
    elif s == "":
        return None
    else:
        return s


def get_dict_value(obj, key, value):
    if obj == None:
        return value
    if key in obj:
        return obj[key]
    else:
        return value


def read_sheet_data(xls_file, sheet_name):
    # 打开excel文件读取数据
    exce = xlrd.open_workbook(xls_file)
    # 获取所有sheet名称
    # print('所有sheet名称', exce.sheet_names())
    sheet1 = exce.sheet_by_name(sheet_name)  # 通过sheet名称获取
    keys = None
    datas = []
    ssk = lambda x: str(x).strip()
    ssv = lambda x: to_value(str(x).strip())
    for i in range(sheet1.nrows):
        if i == 0:
            keys = [ssk(sheet1.cell(i, j).value) for j in range(sheet1.ncols)]
        elif keys != None:
            # print([sheet1.cell(i,j).value for j in range(sheet1.ncols)])
            # print([sheet1.cell(i,j).ctype for j in range(sheet1.ncols)])
            values = [ssv(sheet1.cell(i, j).value) for j in range(sheet1.ncols)]
            datas.append(dict(zip(keys, values)))
    return datas


def get_sheet_names(xls_file):
    # 打开excel文件读取数据
    exce = xlrd.open_workbook(xls_file)
    # 获取所有sheet名称
    return exce.sheet_names()


def read_sheet_params(xls_file, sheet_name):
    # 打开excel文件读取数据
    exce = xlrd.open_workbook(xls_file)
    sheet1 = exce.sheet_by_name(sheet_name)  # 通过sheet名称获取
    keys = None
    datas = None
    ssk = lambda x: str(x).strip()
    ssv = lambda x: to_value(str(x).strip())
    for j in range(sheet1.ncols):
        if j == 0:
            keys = [ssk(sheet1.cell(i, j).value) for i in range(sheet1.nrows)]
        elif keys != None:
            values = [ssv(sheet1.cell(i, j).value) for i in range(sheet1.nrows)]
            datas = dict(zip(keys, values))
    return datas


# excel的子表名称翻译表
xls_name_dict = {
    "巷道": "edges",
    "分支": "edges",
    "节点": "nodes",
    "风扇": "fans",
    "通风机": "fans",
    "风机": "fans",
    "风门": "structs",
    "风窗": "structs",
    "构筑物": "structs",
    "配置参数": "config",
    "配置": "config",
    "参数": "config",
    # 增加其它的名称映射
    "edge": "edges",
    "tunnel": "edges",
    "tunnels": "edges",
    "node": "nodes",
    "vertex": "nodes",
    "vertexs": "nodes",
    "vertexes": "nodes",
    "fan": "fans",
    "gate": "structs",
    "gates": "structs",
    "struct": "structs",
    "option": "config",
    "options": "config",
    "configs": "config",
}

# $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
#   读取分支、风机xlsx文件
def read_xls_file(xls_file):
    sheet_names = get_sheet_names(xls_file)
    json_datas = {}
    config = None
    for name in sheet_names:
        # 子表名翻译成json数据格式中规定的字段名
        key = get_dict_value(xls_name_dict, name, name)
        if key == "config":
            # 读取配置参数
            config = read_sheet_params(xls_file, name)
            config["minQ"] = get_dict_value(config, "minQ", 0.000001)
            config["minH"] = get_dict_value(config, "minH", 0.01)
            config["iterN"] = get_dict_value(config, "iterN", 1000)
            config["fan90"] = get_dict_value(config, "fan90", 0.9)
            # config['password']  = get_dict_value(config, 'password', 0.20200427jl001)
            json_datas[key] = config
        else:
            # 读取分支、风机、节点、构筑物表的数据
            json_datas[key] = read_sheet_data(xls_file, name)
    # # 读取分支数据
    # edges_json = read_sheet_data(xls_file, "分支")
    # # 读取风机数据
    # fans_json = read_sheet_data(xls_file, "风机")
    # # 读取配置参数
    # config = read_sheet_params(xls_file, "配置参数")

    # 构造json数据
    json_datas["sn"] = get_dict_value(config, "sn", "jl2020dxgm001")
    # json_datas = {
    #     "sn": get_dict_value(config, "sn", "jl2020dxgm001"),
    #     "config": config,
    #     "edges": edges_json,
    #     "nodes": None,
    #     "fans": fans_json,
    #     "structs": None,
    # }
    # print(json_datas)
    return json_datas


#!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


def write_xls_file(results, xls_file):
    # 要提取的数据字段
    fields = ["id", "s", "t", "q", "r", "h"]
    # 创建一个workbook 设置编码
    workbook = xlwt.Workbook(encoding="utf-8")
    r1 = results["isOk"]
    r2 = results["error"]
    if r1 == True:
        worksheet = workbook.add_sheet("解算结果")
        edges = results["edges"]
        # for e in edges:
        #     print(te)
        # for tn in tabnodes:
        #     print(tn)
        # 写入标题行
        for j, key in enumerate(fields):
            worksheet.write(0, j, str(key))
        # 写入数据
        for i, e in enumerate(edges):
            for j, key in enumerate(fields):
                worksheet.write(i + 1, j, str(e[key]))
    else:
        # 创建一个记录错误信息的worksheet
        worksheet = workbook.add_sheet("错误信息")
        worksheet.write(0, 0, label=str(r2))
    # 保存
    workbook.save(xls_file)
